package com.peixunp.dao.impl;

import java.util.List;

import java.util.Map;

import com.peixunp.bean.Organizations;
import com.peixunp.dao.OrganizationsDao;
import com.peixunp.util.Constants;

import finaler.db.AbstractBaseDao;
import finaler.util.Page;
import finaler.util.PageList;
import finaler.util.SQLUtil;

public class OrganizationsDaoImpl extends AbstractBaseDao<Organizations> implements OrganizationsDao {

	@Override
	protected boolean isDebug() {
		return Constants.ISDEBUG;
	}
	
	@Override
	protected Class<Organizations> getEntityClass() {
		return Organizations.class;
	}
	/**
	 * 通过城市id,获得该城市有多少个学校
	 */
	@Override
	public int getCitySchoolNum(int cityId) throws Exception {
		String sql  = "select count(*) from organizations where city = '"+cityId+"' and auditflag = '1'";
		return super.getIntBySql(sql);
	}
	@Override
	public PageList getOrgList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" where a.auditFlag = 1");
		if(page.getParameter("cityId")!=null && page.getParameter("cityId").equals("")==false ){
			whereSql.append(" and a.city = "+page.getParameter("cityId"));
		}
		if(page.getParameter("key")!=null && page.getParameter("key").equals("")==false){
			whereSql.append(" and (a.orgname like '%"+page.getParameter("key").toString().trim()+"%' ");
			whereSql.append(" or a.orgshortname like '%"+page.getParameter("key").toString().trim()+"%' ");
			whereSql.append(" or a.orgcontent like '%"+page.getParameter("key").toString().trim()+"%' )");
		}
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from organizations a ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select a.*,b.* ");
		queryListSql.append(" from  organizations a ");
		queryListSql.append(" left join ");
		queryListSql.append(" (select orgid,count(orgid) as rmcount,round(avg(coin)) as avgfen from remark  group by  orgid) b ");
		queryListSql.append(" on  a.id=b.orgid  " );
		queryListSql.append( whereSql );
		queryListSql.append(" order by " );
		queryListSql.append(" avgfen desc," );
		queryListSql.append(" rmcount desc" );
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	/**
	 * 随机一个
	 */
	@Override
	public Organizations getRandomRecord() throws Exception {
		String sql  = "select id from Organizations ORDER BY RAND() LIMIT 1 ";
		return super.findBySqlToBean(sql);
	}

	@Override
	public List<Organizations> getAuditList() throws Exception {
		String sql  = "select * from Organizations where auditflag = 0";
		return super.findBySqlToBeanList(sql);
	}

	@Override
	public int getwantSum() throws Exception {
		String sql  = "select sum(want) from Organizations where auditflag = 1";
		return super.getIntBySql(sql);
	}
}
